﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;

/// <summary>
///JFSJ 的摘要说明
/// </summary>
public class JFSJ
{
  public JFSJ()
  {
    //
    //TODO: 在此处添加构造函数逻辑
    //
  }

  /// <summary>
  /// 获取此条记录
  /// </summary>
  /// <param name="sID"></param>
  /// <returns></returns>
  public static string GetSKHID(string sID)
  {
    string KHID = "";
    string sSql = "select * from SJLR_jfSJ where jfsjid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      KHID = dr["khid"].ToString();
    }
    dr.Close();
    return KHID;
  }
  public static string GetFKFSID(string sID)
  {
    string KHID = "";
    string sSql = "select * from SJLR_jfSJ where jfsjid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      KHID = dr["fkid"].ToString();
    }
    dr.Close();
    return KHID;
  }

  /// <summary>
  ///  //未交费明细
  /// </summary>
  /// <param name="sKHID"></param>
  /// <returns></returns>
  public static DataTable GetMX(string sKHID)
  {
    string sSql = @"select a.*,b.yqxz from sjlr_cbsj c,sjlr_bqql a,jcxx_yqxz b
where c.cbsjid=a.cbsjid and a.yqxzid=b.yqxzid and ssql<0 and bqql_sfsb=2 and c.khid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    DataSet ds = DbHelperSQL.Query(sSql, cmdParams);
    return ds.Tables[0];
  }

  /// <summary>
  ///  //未交费明细合计
  /// </summary>
  /// <param name="sKHID"></param>
  /// <returns></returns>
  public static DataSet GetQLMX(string sKHID)
  {
    string sSql = @"select b.yqxzid,c.yqxz,b.jg,b.jj,isnull(sum(b.ssql*(-1)),0) ySQL,isnull(sum(ssfy*(-1)),0) JE FROM 
  sjlr_cbsj A join sjlr_bqql B on a.cbsjid=b.cbsjid join jcxx_yqxz c on b.yqxzid=c.yqxzid
  where a.cbsj<=(select max(cbsj) from sjlr_cbsj where khid=@0) and a.khid=@0 and ssfy<0 and bqql_sfsb=2
group by b.yqxzid,c.yqxz,b.jg,b.jj
union all
select null,'合计',null,null,isnull(sum(b.ssql*(-1)),0) ySQL,isnull(sum(ssfy*(-1)),0) JE FROM 
  sjlr_cbsj A join sjlr_bqql B on a.cbsjid=b.cbsjid join jcxx_yqxz c on b.yqxzid=c.yqxzid
  where a.cbsj<=(select max(cbsj) from sjlr_cbsj where khid=@0) and a.khid=@0 and ssfy<0 and bqql_sfsb=2

";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    DataSet ds = DbHelperSQL.Query(sSql, cmdParams);
    return ds;
  }

  /// <summary>
  /// //编号
  /// </summary>
  /// <returns></returns>
  public static string GetBH()
  {
    string sBH = "";
    string sRQ = DateTime.Now.ToString("yyyyMM");
    string sSql = "select isnull(max(bh)+1," + sRQ + "000001) id from sjlr_jfsj " +
 "WHERE substring(cast(bh as varchar),0,7)='" + sRQ + "'";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    if (dr.HasRows)
    {
      dr.Read();
      sBH = dr["id"].ToString();
    }
    dr.Close();
    //else
    //{
    //  string sSql = "select bh from sjlr_jfsj WHERE jfsjid=@0";
    //  SqlParameter[] cmdParams = new SqlParameter[1] { new SqlParameter("@0", SqlDbType.VarChar, 10) };
    //  cmdParams[0].Value = GetID();
    //  SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    //  if (dr.HasRows)
    //  {
    //    dr.Read();
    //    sBH = dr["id"].ToString();
    //  }
    //  dr.Close();
    //}
    return sBH;
  }

  /// <summary>
  /// //交费明细合计
  /// </summary>
  /// <param name="sID"></param>
  /// <returns></returns>
  public static DataSet GetJFMX(string sID)
  {
    string sSql = @"select jf_yqxz yqxz,jf_dj,jf_jj,sum(jf_yql) yql,sum(jf_je) je FROM sjlr_jfmx where jf_fksjid=@0
group by jf_yqxz,jf_dj,jf_jj
union all
select '合计',null,null,isnull(sum(jf_yql),0) yql,isnull(sum(jf_je),0) FROM
sjlr_jfmx where jf_fksjid=@0
";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    DataSet ds = DbHelperSQL.Query(sSql, cmdParams);
    return ds;
  }

  /// <summary>
  /// //上次结余
  /// </summary>
  /// <param name="sKHID"></param>
  /// <returns></returns>
  public static string GetSCJY(string sKHID,string jfsjid)
  {
    string s = "0";
    string sSql = @"select a.khid,bcjyk from sjlr_jfsj a
     where a.jfsjid=(select max(jfsjid) from sjlr_jfsj where khid=@0 and jfsjid<@1) and a.khid=@0";
    SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20),
      new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = jfsjid;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      s = dr["bcjyk"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// //本次结余
  /// </summary>
  /// <param name="sID"></param>
  /// <returns></returns>
  public static string GetBCJY(string sID)
  {
    string s = "0";
    string sSql = @"select * from sjlr_jfsj a where a.jfsjid=@0";
    SqlParameter[] cmdParams = new SqlParameter[1] { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      s = dr["bcjyk"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// //实收
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  //  public static double[] GetSS(string sKHID, string sRQ)
  //  {
  //    string sSql = @"select isnull(sum(jf_yql),0) ssql,isnull(sum(ceiling(jf_yql*jf_dj*100)/100),0) ssjg,
  //isnull(sum(ceiling(jf_yql*jf_jj*100)/100),0) ssjj,isnull(sum(jf_je),0) ssfy
  //from sjlr_jfsj a,sjlr_jfmx b where a.jfsjid=b.jf_fksjid and a.khid=@0 and convert(varchar(6),a.jfsj,112)=@1";
  //    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
  //    cmdParams[0].Value = sKHID;
  //    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
  //    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
  //    double[] d = new double[4] { 0, 0, 0, 0 };
  //    if (dr.Read())
  //    {
  //      d[0] = Convert.ToDouble(dr["ssql"].ToString());
  //      d[1] = Convert.ToDouble(dr["ssjg"].ToString());
  //      d[2] = Convert.ToDouble(dr["ssjj"].ToString());
  //      d[3] = Convert.ToDouble(dr["ssfy"].ToString());
  //    }
  //    dr.Close();
  //    return d;
  //  }

  /// <summary>
  /// //本月实缴
  /// </summary>
  /// <param name="sKHID"></param>
  /// <param name="sRQ"></param>
  /// <returns></returns>
  public static string GetBQSJHJ(string sKHID, string sRQ)
  {
    string s = "0";
    string sSql = @"select isnull(sum(bqsj),0) bqsj from sjlr_jfsj a where a.khid=@0 and convert(varchar(6),a.jfsj,112)=@1";
    SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20),
      new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      s = dr["bqsj"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 
  /// </summary>
  /// <param name="year"></param>
  /// <param name="month"></param>
  /// <returns></returns>
  public static string GetJFYJF(string year, string month)
  {
    string sSql = "select count(distinct khid) a from sjlr_JFsj where convert(varchar(7),jfsj,23)='" + year + "-" + month + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 本年本月应收费数量
  /// </summary>
  /// <param name="year"></param>
  /// <param name="month"></param>
  /// <returns></returns>
  public static string GetYISF(string year, string month)
  {
    string sSql = "select sum(bqsj) a from sjlr_JFsj where convert(varchar(7),jfsj,23)='" + year + "-" + month + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// //缴费已计费客户数量
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static string GetJFYJF(string year)
  {
    string sSql = "select count(distinct khid) a from sjlr_JFsj where convert(varchar(4),jfsj,23)='" + year + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  ///  //应收费合计
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static string GetYISF(string year)
  {
    string sSql = "select sum(bqsj) a from sjlr_JFsj where convert(varchar(4),jfsj,23)='" + year + "' ";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    string s = "0";
    if (dr.Read())
    {
      s = dr["a"].ToString();
    }
    dr.Close();
    return s;
  }

  /// <summary>
  /// 缴费记录列表
  /// </summary>
  /// <param name="date1"></param>
  /// <param name="date2"></param>
  /// <returns></returns>
  public static DataSet GetDS_JFJL(string date1, string date2, string user)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(@"select  ROW_NUMBER() OVER (ORDER BY jfSJid desc) AS xh,a.KHID,A.BQSJ,A.BCJYK,A.JFSJID,
convert(varchar,a.JFSJ,23) SJ,B.XM,C.KHMC,c.KH,c.IC,c.XXDZ,c.YQXZ,d.FKFS
from sjlr_jfsj a,sys_user b,(select * from jcxx_khxx a OUTER APPLY f_yqxz_hb(A.KHID)) c,jcxx_fkfs d
WHERE a.dlm=b.dlm and a.khid=c.khid and a.jfsj>=@1 and a.jfsj<cast(@2 as datetime)+1 and a.fkid=d.fkid ");
    if (user != "")
      sbSql.Append("and a.dlm='" + user + "' and c.ic='否'");
    //sbSql.Append("order by a.jfsjid desc");
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                               new SqlParameter("@2", SqlDbType.VarChar, 10)};
    cmdParams[0].Value = SysManage.Get_User;
    cmdParams[1].Value = date1;
    cmdParams[2].Value = date2;
    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
    return ds;
  }

  /// <summary>
  /// 收费率
  /// </summary>
  /// <param name="year"></param>
  /// <returns></returns>
  public static SqlDataReader GetSFL(string year)
  {
    string sSql = @"select a.yf,a.jf*100/(case when b.cb=0 then null else b.cb end) sfl from 
(select substring(convert(varchar(6),jfsj,112),5,2) yf,count(distinct a.khid) jf from sjlr_jfsj a,jcxx_khxx b 
where convert(varchar(4),jfsj,23)='{0}' and a.khid=b.khid and b.ic='否' group by substring(convert(varchar(6),jfsj,112),5,2)) a,
(select substring(convert(varchar(6),cbsj,112),5,2) yf,count(distinct a.khid) cb from sjlr_cbsj a,jcxx_khxx b 
where convert(varchar(4),cbsj,23)='{0}' and a.khid=b.khid and b.ic='否' group by substring(convert(varchar(6),cbsj,112),5,2)) b
where a.yf=b.yf";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(string.Format(sSql, year));
    return dr;
  }

  /// <summary>
  /// 已收费用户合计
  /// </summary>
  /// <param name="month"></param>
  /// <returns></returns>
  public static SqlDataReader GetYSFYH(string year, string month)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("select khjb,ic,count(distinct c.khid) a from jcxx_khxx a,jcxx_khjb b,sjlr_jfsj c ");
    sbSql.Append("where a.khjbid=b.khjbid and a.khid=c.khid and convert(varchar(6),jfsj,112)='"
      + year + month + "' ");
    sbSql.Append("group by khjb,ic");
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sbSql.ToString());
    return dr;
  }

  /// <summary>
  /// 本月完成收费合计
  /// </summary>
  /// <param name="month"></param>
  /// <returns></returns>
  public static SqlDataReader GetWCSF(string year, string month)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("select khjb,ic,sum(bqsj) a from jcxx_khxx a,jcxx_khjb b,sjlr_jfsj c ");
    sbSql.Append("where a.khjbid=b.khjbid and a.khid=c.khid and convert(varchar(6),jfsj,112)='"
      + year + month + "' ");
    sbSql.Append("group by khjb,ic");
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sbSql.ToString());
    return dr;
  }

  //上年结转
  public static string GetSNJZ(string sKHID, string nf)
  {
    string s = "0";
//    string sSql = @"select bcjyk snjz from sjlr_jfsj a
//where jfsj=(select max(jfsj) from sjlr_jfsj where khid=@0 and convert(varchar(4),jfsj,112)=@1) and a.khid=@0";
    string sSql = @"select (ssfy-ysfy) snjz from 
(select sum(ysfy) ysfy from sjlr_cbsj a,sjlr_bqql b
 where a.cbsjid=b.cbsjid and khid=@0 and bqql_sfsb=2 and convert(varchar(4),a.cbsj,112)<@1 ) a,
 (select sum(bqsj) ssfy from sjlr_jfsj where khid=@0 and convert(varchar(4),jfsj,112)<@1) b";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 4) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = Convert.ToInt32(nf);
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      s = string.IsNullOrEmpty(dr["snjz"].ToString()) ? "0" : dr["snjz"].ToString();
    }
    dr.Close();
    return s;
  }

  //此日期之后录入新数据
  public static bool ExistHQID(string jsfjid, string khid)
  {
    bool flag = false;
    string sSql = "select jfsjid as num from sjlr_jfsj where jfsjid>@0 and khid=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20),
                                new SqlParameter("@2", SqlDbType.VarChar, 20)};
    cmdParams[0].Value = jsfjid;
    cmdParams[1].Value = khid;
    cmdParams[2].Value = DateTime.Now.ToString("yyyy-MM-dd");
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }
  //是否过期
  public static bool SFGQ(string jsfjid)
  {
    bool flag = true;
    string sSql = "select jfsjid as num from sjlr_jfsj where jfsjid=@0 and jfsj+2>=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20)};
    cmdParams[0].Value = jsfjid;
    cmdParams[1].Value = DateTime.Now.ToString("yyyy-MM-dd");
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = false;
    }
    dr.Close();
    return flag;
  }
}
